/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class UserQuery {
    private Connection conn;
    public User user=new User();
    private String username;
    private String password;
    private String name;
    private String position;
    private String searchBy;
    public UserQuery(Connection conn){
        this.conn=conn;
    }
    public boolean loginSuccess(String username, String password){
        String query="select concat(firstName,' ' ,lastName) AS 'name', position, username, password FROM companypersonneldata where username = ? AND password = ?";
        boolean loginSuccess=true;
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, username);
            prest.setString(2, password);
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                user.setUsername(username);
                user.setPassword(password);
                user.setName(rs.getString("name"));
                user.setPosition(rs.getString("position"));
                return loginSuccess;
            }
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return !loginSuccess;
    }
    public boolean canAddUser(String firstName, String lastName){
        String query="select firstName, lastName FROM companypersonneldata where firstName = ? AND lastName = ?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, firstName);
            prest.setString(2, lastName);
            ResultSet rs=prest.executeQuery();
            while(rs.next())
                return false;
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    public void addUser(String firstName, String lastName, String position, String username, String password){
        String query="insert into companypersonneldata(firstName, lastName, position, username, password) values(?,?,?,?,?)";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, firstName);
            prest.setString(2, lastName);
            prest.setString(3, position);
            prest.setString(4, username);
            prest.setString(5, password);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added " + firstName + " " + lastName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public List<User> initializeUsers(){
        List<User> users=new ArrayList<User>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from companypersonneldata ORDER BY lastName ASC";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                User systemUser=new User();
                systemUser.setFirstName(rs.getString("firstName"));
                systemUser.setLastName(rs.getString("lastName"));
                systemUser.setPosition(rs.getString("position"));
                systemUser.setUsername(rs.getString("username"));
                systemUser.setPassword(rs.getString("password"));
                users.add(systemUser);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return users;
    }
    public void editUser(String firstName, String lastName, String position, String username, String password, String origFirstName, String origLastName){
        String query="update companypersonneldata set firstName=?, lastName=?, position=?, username=?, password=? where firstName=? AND lastName=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, firstName);
            prest.setString(2, lastName);
            prest.setString(3, position);
            prest.setString(4, username);
            prest.setString(5, password);
            prest.setString(6, origFirstName);
            prest.setString(7, origLastName);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited profile of " + firstName + " " + lastName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void interpretSearchBy(int n){
        if(n==0)
            searchBy="All";
        else if(n==1)
            searchBy="Name";
        else if(n==2)
            searchBy="Position";
        else if(n==3)
            searchBy="Username";
    }
    public List<User> search(String text){
        List<User> users=new ArrayList<User>();
        String query;
        if(searchBy.equals("Name"))
            query="select * FROM companypersonneldata where concat(firstName,' ' ,lastName) like ? ORDER BY lastName ASC";
        else if(searchBy.equals("Position"))
            query="select * FROM companypersonneldata where position like ? ORDER BY lastName ASC";
        else
            query="select * FROM companypersonneldata where username like ? ORDER BY lastName ASC";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, "%"+text+"%");
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                User systemUser=new User();
                systemUser.setName(rs.getString("firstName")+" "+rs.getString("lastName"));
                systemUser.setFirstName(rs.getString("firstName"));
                systemUser.setLastName(rs.getString("lastName"));
                systemUser.setPosition(rs.getString("position"));
                systemUser.setUsername(rs.getString("username"));
                systemUser.setPassword(rs.getString("password"));
                users.add(systemUser);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(UserQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(users.isEmpty()){
            JOptionPane.showMessageDialog(null, "The keyword that you have just typed in does not belong to any user or falls under the wrong category. Please check your input.");
            return null;
        }
        return users;
    }
    public void setUsername(String username){
        this.username=username;
    }
    public void setPassword(String password){
        this.password=password;
    }
    public void setName(String name){
        this.name=name;
    }
    public void setPosition(String position){
        this.position=position;
    }
    public String getUsername(){
        return username;
    }
    public String getPassword(){
        return password;
    }
    public String getName(){
        return name;
    }
    public String getPosition(){
        return position;
    }
}
